package com.laxcen.smoke.dao;

import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface GetForSdkMessageDao {

    //添加异常表(有格子)
    @Insert("insert into abnormalrecord(id,smartno,smartboxno,abnormaltype,createtime,content,state) values(#{id},#{smartno},#{sboxno},#{abnormaltype},#{createtime},#{content},#{state})")
    int insertannormal(String id, String smartno, String sboxno, int abnormaltype, Date createtime, String content, int state);

    //更改智能柜状态 0正常 1异常
    @Update("update smart set status = #{status} where no = #{smartno}")
    int updatesmartstatus(String smartno, int status);

    //获取指定智能柜编号下的主柜id
    @Select("select sbd.id from smartboard sbd right join real_smart_smartboard rss on sbd.id = rss.boardid right join smart sm on rss.smartid = sm.id where sm.no = #{smartno} and sbd.specification = 0")
    String getsmartboardid(String smartno);

    @Update("update smartboard set status =  #{status} where id = #{id}")
    int updatesmartboard(String id, int status);

    //添加异常表(没格子)
    @Insert("insert into abnormalrecord(id,smartno,abnormaltype,createtime,content,state) values(#{id},#{smartno},#{abnormaltype},#{createtime},#{content},#{state})")
    int insertannormalNullBox(String id, String smartno, int abnormaltype, Date createtime, String content, int state);


    @Insert("insert into getforsdkmessage(id,topic,messageid,content,type,createtime) values(#{id},#{topic},#{messageid},#{content},#{type},#{createtime}) ")
    int insert(@Param("id") String id, @Param("topic") String topic, @Param("messageid") String messageid, @Param("content") String content, @Param("type") int type, @Param("createtime") Date createtime);

//    //添加操作订单详情表
    @Insert("insert into operaterecorddetailtable(id,recordid,smartno,boxno,optime,optype,opresult,createtime) values(#{id},#{recordid},#{smartno},#{boxno},#{optime},#{optype},#{opresult},#{createtime}) ")
    int insertuporderdetail(String id, String recordid, String smartno, String boxno, Date optime, int optype, int opresult, Date createtime);

    //添加操作订单表
    @Insert("insert into operaterecord(id,smartno,boxno,opid,optime,optype,orderno,createtime) values(#{id},#{smartno},#{boxno},#{opid},#{optime},#{optype},#{orderno},#{createtime}) ")
    int insertuporder(String id, String smartno, String boxno, String opid, Date optime, int optype, String orderno, Date createtime);

    //添加操作视频左到操作表
    @Update("update operaterecord set VideoLeft = #{video} where OrderNo = #{orderno} and SmartNo = #{smartno} and OpId = #{opid} and optype = #{optype}")
    int insertopervideoleft(String video, String orderno, String smartno, String opid, int optype);

    //添加操作视频右到操作表
    @Update("update operaterecord set VideoRight = #{video} where OrderNo = #{orderno} and SmartNo = #{smartno} and OpId = #{opid} and optype = #{optype}")
    int insertopervideoright(String video, String orderno, String smartno, String opid, int optype);

    //修改APP状态表APP当前版本
    @Update("update issuestatus set localversion = #{localversion} where smartno = #{smartno}")
    int updateissuestatus(String localversion, String smartno);

    //修改订单取出状态
    @Update("update operaterecord set optype = 1 where smartno = #{smartno} and boxno = #{boxno} and orderno = #{orderno}")
    int updateOrderStatus(String smartno, String boxno, String orderno);

    //修改格子状态
    @Update("update smartbox set status = #{status} where id =(select a.id from (select sb.id from smartbox sb right join real_board_box rbb on sb.id = rbb.BoxId right join real_smart_smartboard rss on rbb.BoardId = rss.BoardId right join smart sm on rss.SmartId = sm.id where sm.no = #{smartno} and sb.no = #{boxno}) a)")
    int updateBoxStatus(int status, String smartno, String boxno);

    //修改当前智能柜的版本号
    @Update("update smart set versionid = #{versionid} where no = #{smartno}")
    int updateVersionIdBySmNo(String versionid, String smartno);

    //关联查询修改格子状态
    @Update("update smartbox set status = #{status} where id = #{id}")
    int updateBoxStatusGL(int status, String id);

    //关联查询修改实体柜状态
    @Update("update smartboard set status = #{status} where id = #{id}")
    int updateBoardStatusGL(int status, String id);

    //修改APP状态表APP当前版本
    @Update("update issuestatus set status = #{status} where smartno = #{smartno}")
    int updateissstatus(int status, String smartno);

    //修改实体柜状态
    @Update("update smartboard set status = #{status}  where id =(select a.id from (select smb.id from smartboard smb right join real_board_box rbb on smb.id = rbb.BoardId right join real_smart_smartboard rss on rbb.BoardId = rss.BoardId right join smart sm on rss.SmartId = sm.id left join smartbox sbox on rbb.BoxId = sbox.id where sm.no = #{smartno} and sbox.no = #{boxno}) a)")
    int updateBoardStatus(int status, String smartno, String boxno);

    //修改订单详情取出状态
    @Update("update operaterecorddetailtable set optype = 1,opresult = 1 where smartno = #{smartno} and boxno = #{boxno} ")
    int updateOrderDeatilStatus(String smartno, String boxno);

    @Update("update smartboard set UpsStatus = #{upsstatus},UpsElectricity = #{upselectricity} where id = #{id}")
    int updateUpsForSmart(int upsstatus, String upselectricity, String id);

    //查询APP升级状态中APP当前版本
    @Select("select localversion from issuestatus where smartno = #{smartno}")
    String getlocalversion(String smartno);

    //查询APP升级状态中计划升级版本
    @Select("select newversion from issuestatus where smartno = #{smartno}")
    String getnewversion(String smartno);

    @Select("select f_username from sys_user where f_id = #{fid}")
    String getIdFromUser(String fid);

    @Select("select id from operaterecord where smartno = #{smartno} and boxno = #{boxno} and orderno =#{orderno} and optype = 0")
    String getIdByDis(String smartno, String boxno, String orderno);

    @Select("select adverimg from advertisement")
    String getAdverBySmartNo();

    //查询当前版本号的版本id
    @Select("select id from issue where versionno = #{versionno}")
    String getVersionIdByVersionNo(String versionno);

    @Update("update smart set status = #{status} where no = #{smartno}")
    int updateSmartByNo(int status, String smartno);

    @Update("update smartboard set status = #{status} where id = #{id}")
    int updatesmartBoardBy(int status, String smartno, String id);

    //获取devicename轮询
    @Select("select distinct sm.no smartno from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId  ")
    List<String> getSmartNo();

    //根据智能柜编号和格子编号查询格子id
    @Select("select a.id from (select sb.id from smartbox sb right join real_board_box rbb on sb.id = rbb.BoxId right join real_smart_smartboard rss on rbb.BoardId = rss.BoardId right join smart sm on rss.SmartId = sm.id where sm.no = #{smartno} and sb.no = #{boxno}) a")
    List<String> getBoxIdBySmartNoAndBoxNo(String smartno, String boxno);

    //根据智能柜编号和格子编号查询实体柜id
    @Select("select a.id from (select distinct smb.id from smartboard smb right join real_board_box rbb on smb.id = rbb.BoardId right join real_smart_smartboard rss on rbb.BoardId = rss.BoardId right join smart sm on rss.SmartId = sm.id left join smartbox sbox on rbb.BoxId = sbox.id where sm.no = #{smartno} and sbox.no = #{boxno} ) a")
    List<String> getBoardIdBySmartNoAndBoxNo(String smartno, String boxno);

    @Select("select a.id from (select sb.id from smartboard sb right join  real_smart_smartboard rss  on rss.BoardId = sb.id left join smart sm on rss.SmartId = sm.id where sm.no = #{smartno})a")
    List<String> getSmartboardidBySmartNo(String smartno);

    @Select("select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid ")
    List<String> getAbn();

    @Select("select sbox.id from smartbox sbox left join real_board_box rbb on sbox.id = rbb.BoxId left join real_smart_smartboard rss on rbb.BoardId = rss.BoardId left join smart sm on rss.SmartId = sm.id   where sbox.status = #{status} and sm.no = #{smartno} ")
    List<String> getNotState(String smartno, int status);

    @Select("select distinct sbd.id from smartbox sbox left join real_board_box rbb on sbox.id = rbb.BoxId left join smartboard sbd on rbb.BoardId = sbd.id left join real_smart_smartboard rss on rbb.BoardId = rss.BoardId left join smart sm on rss.SmartId = sm.id   where sbox.status <> #{status} and sm.no = #{smartno}  ")
    List<String> getIdBySmartno(String smartno, int status);

}
